/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package assignment.process;

import assignment.jaxb.categories.CategoryType;
import assignment.jaxb.categories.Categories;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.Serializable;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Marshaller;

/**
 *
 * @author tri
 */
public class categoriesSAXProcessor implements Serializable {

  Categories categories = new Categories();
  String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  String url = "jdbc:sqlserver://localhost:1433;instanceName=SQLEXPRESS;databaseName=ShoppingDB";

  public Connection getConnection() {
    Connection con = null;
    try {
      //   Properties prop = new Properties();
      //   prop.load(new FileInputStream(fileName));
      // String driver = prop.getProperty("driver");
      // String url = prop.getProperty("url");
      Class.forName(driver);
      con = DriverManager.getConnection(url, "sa", "");

    } catch (ClassNotFoundException ex) {
      Logger.getLogger(productSAXProcessor.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
      Logger.getLogger(productSAXProcessor.class.getName()).log(Level.SEVERE, null, ex);
    }
    return con;
  }

  public categoriesSAXProcessor() {

    try {
      Connection con = getConnection();
      String sql = "SELECT id, name, imageThumbnail FROM Category";
      Statement stm = con.createStatement();
      ResultSet rs = stm.executeQuery(sql);

      while (rs.next()) {
        CategoryType category = new CategoryType();

        int id = rs.getInt("id");
        category.setId(new BigInteger(String.valueOf(id)));
        category.setName(rs.getString("name "));
        category.setImageThumbnail(rs.getString("imageThumbnail"));

        categories.getCategory().add(category);
      }

    } catch (SQLException ex) {
      Logger.getLogger(productSAXProcessor.class.getName()).log(Level.SEVERE, null, ex);
    }
  }

  public void marshallXMLProduct(String filename) {
    try {
      JAXBContext ctx = JAXBContext.newInstance(Categories.class);
      Marshaller mar = ctx.createMarshaller();
      mar.setProperty(Marshaller.JAXB_ENCODING, "UTF-8");
      mar.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
      mar.marshal(categories, new File(filename));
    } catch (JAXBException ex) {
      Logger.getLogger(productSAXProcessor.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
}
